﻿-- 1
Create database Ass7
Go
Use Ass7
Go
-- 2, 3, 4, 5
Create table Course_Details(
	Lop		nvarchar(10) Constraint PK_Lop Primary Key,
	Ngay	int Not null,
	Thang	int Not null,
	Nam		int Not null,
	GV		varchar(10) Constraint FK_GV Foreign Key
						 REFERENCES Employee_Details(MaNV)
)

Create table Student_Details(
	SBD			int identity(450,1) Constraint PK_SBD Primary key,
	Ten			nvarchar(50) NOT NULL,
	Lop			nvarchar(10) NOT NULL,
	NgaySinh	varchar(20),
	SDT			char(10) null,
	Email		varchar(50) default 'No Email',
)

Create table Marks_Details(

	SBD		int Constraint FK_SBD Foreign Key
								REFERENCES Student_Details(SBD),
	Mon		varchar(10) NOT NULL,
	LanThi	int,
	Diem	int	default 0,
	Constraint PK_SBD_Sub Primary Key(SBD,Mon)
)

Create table Employee_Details(
	MaNV	varchar(10) Constraint PK_MaNV primary key,
	Ten		nvarchar(50),
	NgVL	smalldatetime,
	Phong	varchar(50)
)

Create table Salary_Details(
	MaNV	varchar(10) Constraint FK_MaNV_Salary Foreign Key
						REFERENCES Employee_Details(MaNV),
	Thang	int,
	Nam		int,
	HeSo	int,
	Luong	money,
	GhiChu	varchar(50),
	Constraint PK_MaNV_Thang_Nam Primary Key(MaNV,Thang,Nam)
)

-- 6


Insert into Employee_Details(MaNV,Ten,NgVL,Phong)
			values('LTN','Lê Thanh Nhân',1999,'Giáo Viên')

Insert into Salary_Details(MaNV,Thang,Nam,HeSo,Luong,GhiChu)
			values('LTN',11,2013,5,15000000,'')

Insert into Course_Details(Lop,Ngay,Thang,Nam,GV)
			values('F2-1308',13,08,2013,'LTN')

Insert into Student_Details(Ten,Lop,NgaySinh,SDT,Email)			
			values(N'Giàng A Tóng','F2-1308','20/09/1994','0908095445','giangatonglk@gmail.com')
Insert into Student_Details(Ten,Lop,NgaySinh,SDT,Email)		
			values(N'Nguyễn Quỳnh Trang','F2-1308','21/09/1994','0973596979','nguyenquynhtrang@gmail.com')
Insert into Student_Details(Ten,Lop,NgaySinh,SDT,Email)		
			values(N'Trần Hạo Nam','F2-1308','22/09/1994','0932777778','tranhaonam@gmail.com')
Insert into Student_Details(Ten,Lop,NgaySinh,SDT,Email)		
			values(N'Thành Long','F2-1308','23/09/1994','0945325445','thanhlong@gmail.com')
Insert into Student_Details(Ten,Lop,NgaySinh,SDT,Email)		
			values(N'Lý Tiểu Long','F2-1308','24/09/1994','0968841551','lytieulong@gmail.com')

Insert into Marks_Details
			values('SQL',1,100)
Insert into Marks_Details
			values('SQL',1,95)
Insert into Marks_Details
			values('SQL',1,85)
Insert into Marks_Details
			values('SQL',1,75)
Insert into Marks_Details
			values('SQL',1,65)

Select *
From Student_Details;

Select *
From Marks_Details;

Select *
From Employee_Details;

Select *
From Salary_Details;

Select *
From Course_Details;
-- 7

Alter table Student_Details add constraint C_SDT Check (SDT like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') 
Alter table Student_Details add constraint FK_Lop Foreign Key(Lop) References Course_Details(Lop)

Delete From Marks_Details
Where LanThi < 40